# upload_to_sql.py
import time

import pandas as pd
from MAP_INFO import MAP_INFO
from UPLOAD_RULES import APPEND_TABLES, REPLACE_TABLES
import traceback
import uuid
from datetime import datetime
DEFAULT_SCHEMA = "DATAFILL"






def map_fields(df: pd.DataFrame, sheet_name: str) -> pd.DataFrame:
    df = df.copy()

    # 清理列名：去除空格、回车和换行符
    df.columns = [str(c).strip().replace("\r", "").replace("\n", "") for c in df.columns]

    # 获取该工作表的字段映射
    field_mapping = MAP_INFO.get(sheet_name, {}).get('fields', {})

    # 创建一个映射字典，只包含在 DataFrame 中且存在映射的列
    rename_map = {cn: en for cn, en in field_mapping.items() if cn in df.columns}

    # 打印映射信息
    #if rename_map:
        #print(f"✅ 工作表[{sheet_name}] 字段映射成功 {len(rename_map)}/{len(field_mapping)} 个")
        #for cn, en in rename_map.items():
            #print(f"    {cn} -> {en}")
    #else:
        #print(f"⚠️ 工作表[{sheet_name}] 未匹配到任何字段映射（列名：{df.columns.tolist()}）")

    # 仅保留有映射的列，去掉没有映射的列
    df = df[[col for col in rename_map.keys()]]  # 仅保留映射过的列
    df = df.rename(columns=rename_map)  # 对保留的列进行名称映射

    return df







def upload_to_sql(df: pd.DataFrame, sheet_name: str, conn,front_uuid):

    if sheet_name in APPEND_TABLES:
        mode = 'append'
    elif sheet_name in REPLACE_TABLES:
        mode = 'replace'
    else:
        raise ValueError(f"❌ `{sheet_name}` 未指定上传模式，请添加到 APPEND_TABLES 或 REPLACE_TABLES")


    table_name = MAP_INFO.get(sheet_name, {}).get('table')
    if not table_name:
        raise ValueError(f"❌ `{sheet_name}` 未配置表名 MAP_INFO['{sheet_name}']['table']")

    # 3) 字段映射
    mapped_df = map_fields(df, sheet_name)
    #print(mapped_df)


    # 5) 真正写入
    safe_upload_to_dm(mapped_df, table_name, conn,front_uuid, mode=mode)
    print(f"✅ 表 {table_name}` 已以 `{mode}` 模式入库")


'''def safe_upload_to_dm(df, table_name, conn,front_uuid,company_code, equipment_code, mode='append'):



    if df is None or df.empty:
        print(f"⚠️ 表 {table_name} 无数据，跳过")
        return


    cur = conn.cursor()
    try:
        # replace：先清空表，再插入
        if mode == 'replace':
            try:
                cur.execute(f'TRUNCATE TABLE "{DEFAULT_SCHEMA}"."{table_name}"')
                conn.commit()
                print(f"🔄 已清空表 {table_name}（TRUNCATE）")
            except Exception:
                conn.rollback()
                cur.execute(f'DELETE FROM "{DEFAULT_SCHEMA}"."{table_name}"')
                conn.commit()
                print(f"🔄 已清空表 {table_name}（DELETE）")
        elif mode != 'append':
            raise ValueError(f"不支持的 mode: {mode}")

        base_cols = list(df.columns)  # 映射后英文列
        add_id = (table_name not in ["status_information", "tbl_sss_company_info", "tbl_sss_device"])
        cols = (["id", "uuid"] + base_cols) if add_id else (["uuid"] + base_cols)  # 在这里添加 uuid 列


        cols_sql = ", ".join(f'"{c}"' for c in cols)
        placeholders = ", ".join(["?"] * len(cols))   # 与列数一致
        sql = f'INSERT INTO "{DEFAULT_SCHEMA}"."{table_name}" ({cols_sql}) VALUES ({placeholders})'
        print(f"正在使用表名: {table_name}")
        print(f"生成的 SQL 查询：{sql}")


        data = []
        for row in df.itertuples(index=False, name=None):
            # NaN -> None（保持你原有逻辑）
            cleaned = tuple(None if pd.isna(v) else v for v in row)

            if add_id:
                if table_name=="tbl_sss_company_info":
                    row_with_id = (company_code, front_uuid) + cleaned
                elif table_name=="tbl_sss_device":
                    row_with_id = (equipment_code,front_uuid) + cleaned
                #else:
                # 对于非 status_information 表，添加生成的 id 和前端传入的 uuid
                   # row_with_id = (uuid.uuid4().hex, front_uuid) + cleaned  # 32 位，无短横线，避免被 VARCHAR(32) 截断
                #data.append(row_with_id)
                else:
                                # 确保每行生成新的 UUID
                    new_uuid = uuid.uuid4().hex
                    print(f"生成的新 UUID: {new_uuid}")  # 打印生成的 UUID，检查是否唯一
                    row_with_id = (new_uuid, front_uuid) + cleaned  # 每行生成新的 UUID
                data.append(row_with_id)

        # 执行插入操作

            cur.executemany(sql, data)
            conn.commit()
            print(f"✅ 表 {table_name} 成功写入 {len(data)} 行（使用列：{cols}）")

    except Exception as e:
        print(f"❌ 错误发生：{e}")
        print(f"错误语句：{sql}")
        print(f"错误数据：{data}")
        conn.rollback()
        # 打印异常堆栈信息

        print("堆栈信息：")
        print(traceback.format_exc())  # 打印详细的堆栈信息

        raise
    finally:
        cur.close()'''


def safe_upload_to_dm(df, table_name, conn, front_uuid,  mode='append'):
    if df is None or df.empty:
        print(f"⚠️ 表 {table_name} 无数据，跳过")
        return

    cur = conn.cursor()
    try:
        # replace：先清空表，再插入
        if mode == 'replace':
            try:
                cur.execute(f'TRUNCATE TABLE "{DEFAULT_SCHEMA}"."{table_name}"')
                conn.commit()
                print(f"🔄 已清空表 {table_name}（TRUNCATE）")
            except Exception:
                conn.rollback()
                cur.execute(f'DELETE FROM "{DEFAULT_SCHEMA}"."{table_name}"')
                conn.commit()
                print(f"🔄 已清空表 {table_name}（DELETE）")
        elif mode != 'append':
            raise ValueError(f"不支持的 mode: {mode}")

        base_cols = list(df.columns)  # 映射后英文列
        add_id = (table_name not in ["status_information"])
        cols = (["id", "uuid"] + base_cols) if add_id else (["uuid"] + base_cols)  # 在这里添加 uuid 列
        cols_sql = ", ".join(f'"{c}"' for c in cols)
        placeholders = ", ".join(["?"] * len(cols))   # 与列数一致
        sql = f'INSERT INTO "{DEFAULT_SCHEMA}"."{table_name}" ({cols_sql}) VALUES ({placeholders})'
        print(f"正在使用表名: {table_name}")
        print(f"生成的 SQL 查询：{sql}")

        data = []
        for row in df.itertuples(index=False, name=None):
            # NaN -> None
            cleaned = tuple(None if pd.isna(v) else v for v in row)
            # 确保每个字符串字段是UTF-8编码
            #cleaned = tuple(str(v).encode('utf-8', 'ignore').decode('utf-8') if isinstance(v, str) else v for v in cleaned)


            if add_id:

                    # 确保每行生成新的 UUID
                    new_uuid = uuid.uuid4().hex
                    #print(f"生成的新 UUID: {new_uuid}")  # 打印生成的 UUID，检查是否唯一
                    row_with_id = (new_uuid, front_uuid) + cleaned  # 每行生成新的 UUID
                    data.append(row_with_id)
            else:
                    # if table_name == "tbl_sss_company_info":
                    #         row_with_id = (company_code, front_uuid) + cleaned
                    # elif table_name == "tbl_sss_device":
                    #         row_with_id = (equipment_code, front_uuid) + cleaned

                    if table_name == "status_information":
                            row_with_id = (str(front_uuid),) + cleaned

                    data.append(row_with_id)

        # 打印检查 data 是否为空
        #if data:
           # print(f"准备插入的数据：{data[:5]}")  # 打印前 5 行检查数据内容

        # 执行插入操作
        if data:
            new_data = []
            if '"tbl_sss_device"' in sql:
                for d in data:
                    new_data.append((d[5],) + d[1:])
                cur.executemany(sql, new_data)
                conn.commit()

            elif '"tbl_sss_company_info"' in sql:
                for d in data:
                    new_data.append((d[2],) + d[1:])
                cur.executemany(sql, new_data)
                conn.commit()

            elif '"tbl_iss_employ"' in sql:
                for d in data:
                    new_data.append((d[8],) + d[1:])
                cur.executemany(sql, new_data)
                conn.commit()

            else:
                cur.executemany(sql, data)
                conn.commit()
            #print(f"✅ 表 {table_name} 成功写入 {len(data)} 行（使用列：{cols}）")
        else:
            print(f"⚠️ 表 {table_name} 没有数据插入")

    except Exception as e:
        conn.rollback()
        # 打印异常堆栈信息
        print(f"❌ 错误发生：{e}")
        print(f"错误语句：{sql}")
        print(f"错误数据：{data}")
        #print("堆栈信息：")
        #print(traceback.format_exc())  # 打印详细的堆栈信息

        raise
    finally:
        cur.close()
